package cn.sccdlg.spring5.jdbctemplate.jdbctemplate;

import cn.sccdlg.spring5.jdbctemplate.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author RaoShuang
 * @emil 991207823@qq.com
 * @date 2020/10/20 23:21
 *  JdbcTemplate的CRUD操作
 */
public class JdbcTemplateDemo3 {

    public static void main(String[] args) {
        //1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        //2.获取对象
        JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
        //3.执行操作
        //3-1、保存
//        jt.update("insert into account(name,money)values(?,?)", "eee", 3333f);
//        List<Account> query = jt.query("select * from account", new AccountRowMapper());
//        System.out.println(query);

        //3-2、更新
        jt.update("update account set name=?,money=? where id=?", "test", 2223, 7);
        List<Account> query1 = jt.query("select * from account where id = ?", new AccountRowMapper(), 7);
        System.out.println(query1);
        System.out.println("----------------");

        //3-3、删除
        jt.update("delete from account where id=?", 8);
        List<Account> query2 = jt.query("select * from account where id = ?", new AccountRowMapper(), 8);
        System.out.println(query2);
        System.out.println("----------------");

        //3-4、查询所有
        List<Account> accounts = jt.query("select * from account", new AccountRowMapper());
        for (Account account : accounts) {
            System.out.println(account);
        }
        System.out.println("----------------");

        //3-4-2、指定查询范围
        List<Account> accounts2 = jt.query("select * from account where money > ?",
                new BeanPropertyRowMapper<Account>(Account.class), 1000f);
        for (Account account : accounts2) {
            System.out.println(account);
        }
        System.out.println("----------------");

        //查询一个
        List<Account> accounts3 = jt.query("select * from account where id = ?",
                new BeanPropertyRowMapper<Account>(Account.class), 1);
        System.out.println(accounts3.isEmpty() ? "没有内容" : accounts3.get(0));
        System.out.println("----------------");

        //查询返回一行一列（使用聚合函数，但不加group by子句）
        Long count = jt.queryForObject("select count(*) from account where money > ?", Long.class, 1000f);
        System.out.println(count);

    }
}

/**
 * 定义Account的封装策略
 */
class AccountRowMapper implements RowMapper<Account> {
    /**
     * 把结果集中的数据封装到Account中，然后由spring把每个Account加到集合中
     * @param rs
     * @param rowNum
     * @return
     * @throws SQLException
     */
    @Override
    public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
        Account account = new Account();
        account.setId(rs.getInt("id"));
        account.setName(rs.getString("name"));
        account.setMoney(rs.getFloat("money"));
        return account;
    }
}
